package com.anti.modular.bus.indexpage.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.anti.jdbc.DBUtils;
import com.anti.modular.bus.indexpage.param.CwTjParam;
import com.anti.modular.bus.indexpage.result.CwTjAllRes;
import com.anti.modular.bus.indexpage.result.CwTjRes;
import com.anti.modular.bus.indexpage.result.TjResult;
import com.anti.modular.bus.indexpage.result.TsResult;
import com.anti.modular.bus.indexpage.service.BusIndexPageService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Description TODO
 * @Author anTi
 * @Date 2021-6-4
 */
@Service
public class BusIndexPageServiceImpl implements BusIndexPageService {
    @Resource
    DBUtils dbUtils;
    @Override
    public List<TsResult> getTs() {
        String sql="select a.* from (select b.id as bill_id, '1' as type, '收租' as type_name,a.room_name,a.name,a.rent_reminder_day,b.rented_day," +
                "DATE_ADD(b.rented_day,INTERVAL a.rent_reminder_day DAY) as ts_rq" +
                " from house_lease a LEFT JOIN house_bill b on a.id=b.house_lease_id where  " +
                "a.rent_reminder_switch =1 and a.status =0 and a.lease_status =1 and b.status = 0 and b.bill_status in (0,3)" +
                ") a where DATE_FORMAT(a.ts_rq,'%Y-%m-%d')<=DATE_FORMAT(NOW(),'%Y-%m-%d')" +
                " union all" +
                " SELECT b.* from (SELECT b.id as bill_id, '2' as type,'交租' as type_name, a.room_name,a.name,a.rent_reminder_day,b.rented_day," +
                "DATE_ADD(b.rented_day,INTERVAL a.rent_reminder_day DAY) as ts_rq from owner_lease a LEFT JOIN owner_lease_bill b on a.id=b.owner_lease_id where" +
                " a.rent_reminder_switch =1 and a.status =0 and a.lease_status =1 and b.status = 0 and b.bill_status in (1,3)" +
                ") b where  DATE_FORMAT(b.ts_rq,'%Y-%m-%d')<=DATE_FORMAT(NOW(),'%Y-%m-%d')" +
                " union all" +
                " SELECT c.* from (select c.id as bill_id, '3' as type,'其他收支' as type_name, c.room_name,c.name,c.rent_reminder_day,c.should_receive_date as rented_day," +
                "DATE_ADD(c.should_receive_date,INTERVAL c.rent_reminder_day DAY) as ts_rq from  bill_other_revenues c where " +
                "c.status=0 and c.revenues_status=0 and c.rent_reminder_switch =1) c where  DATE_FORMAT(c.ts_rq,'%Y-%m-%d')<=DATE_FORMAT(NOW(),'%Y-%m-%d')";
        return dbUtils.query(sql,TsResult.class);
    }

    @Override
    public List<TsResult> getNowTs() {
        String sql="select a.* from (select b.id as bill_id, '1' as type, '收租' as type_name,a.room_name,a.name,b.rented_day from house_lease a LEFT JOIN house_bill b on a.id=b.house_lease_id where" +
                " a.rent_reminder_switch =1 and a.status =0 and a.lease_status =1 and b.status = 0 and b.bill_status in (0,3)" +
                ") a where DATE_FORMAT(a.rented_day,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') " +
                " union all" +
                " SELECT b.* from (SELECT b.id as bill_id, '2' as type,'交租' as type_name, a.room_name,a.name,b.rented_day from owner_lease a LEFT JOIN owner_lease_bill b on a.id=b.owner_lease_id where" +
                " a.rent_reminder_switch =1 and a.status =0 and a.lease_status =1 and b.status = 0 and b.bill_status in (1,3)" +
                ") b where  DATE_FORMAT(b.rented_day,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d')" +
                " union all" +
                " SELECT c.* from (select c.id as bill_id, '3' as type,'其他收支' as type_name, c.room_name,c.name,c.should_receive_date as rented_day" +
                " from  bill_other_revenues c where c.status=0 and c.revenues_status=0 and c.rent_reminder_switch =1) c where  DATE_FORMAT(c.rented_day,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d')";
        return dbUtils.query(sql,TsResult.class);
    }

    @Override
    public TjResult getTjData() {
        String sql="select \n" +
                "(select count(*) from house_bill where status =0 and bill_status = 0 ) as allBillNum,\n" +
                "(select count(*) from house_bill where status =0 and bill_status = 0 and  \n" +
                "DATE_FORMAT(rented_day,'%Y-%m-%d') =DATE_FORMAT(NOW(),'%Y-%m-%d') ) as nowBillNum,\n" +
                "(select count(*) from house_bill where status =0 and bill_status = 0 and  \n" +
                "DATE_FORMAT(rented_day,'%Y-%m-%d') <DATE_FORMAT(NOW(),'%Y-%m-%d')) as yqBillNum,\n" +
                "(select count(*) from  house_lease where status = 0 and lease_status in (1,3)) as allLeaseNum,\n" +
                "(select count(*) from  house_lease where status = 0 and lease_status in (1,3)\n" +
                "and   DATE_FORMAT(rent_end_date,'%Y-%m-%d') =DATE_FORMAT(NOW(),'%Y-%m-%d')) as nowLeaseNum,\n" +
                "(select count(*) from  house_lease where status = 0 and lease_status in (1,3)\n" +
                "and   DATE_FORMAT(rent_end_date,'%Y-%m-%d') < DATE_FORMAT(NOW(),'%Y-%m-%d')) as yqLeaseNum,\n" +
                "(select  count(*) from room_reservation where status = 0) as allReservationNum,\n" +
                "(select count(*) from  room_reservation where status = 0 \n" +
                "and   DATE_FORMAT(latest_date,'%Y-%m-%d') =DATE_FORMAT(NOW(),'%Y-%m-%d') ) as nowReservationNum,\n" +
                "(select count(*) from  room_reservation where status = 0 \n" +
                "and   DATE_FORMAT(latest_date,'%Y-%m-%d')<DATE_FORMAT(NOW(),'%Y-%m-%d')) as yqReservationNum,\n" +
                "(select count(*) from bus_room where status=0 and lease=0) as allRoomNum\n" +
                " from dual";
        return dbUtils.getFirst(sql,TjResult.class);
    }

    @Override
    public List<CwTjRes> cwTj(CwTjParam cwTjParam) {
        StringBuilder sb=new StringBuilder();
        ArrayList<Object> param = CollectionUtil.newArrayList();
        if (ObjectUtil.isNotNull(cwTjParam)) {
            String startTime = cwTjParam.getDateStrStart();
            String endTime = cwTjParam.getDateStrEnd();
            if(ObjectUtil.isNotEmpty(startTime)&&ObjectUtil.isNotEmpty(endTime)) {
                // 根据房间名 查询
                if (ObjectUtil.isNotEmpty(cwTjParam.getReceiveDateType())) {
                    int receiveDateType = cwTjParam.getReceiveDateType();
                    if (receiveDateType==1){
                        sb.append(" and  DATE_FORMAT(receive_date,'%Y-%m-%d')<=DATE_FORMAT(?,'%Y-%m-%d') and DATE_FORMAT(receive_date,'%Y-%m-%d')>=DATE_FORMAT(?,'%Y-%m-%d')");
                        param.add(endTime);
                        param.add(startTime);
                    }else {
                        sb.append(" and  DATE_FORMAT(should_receive_date,'%Y-%m-%d')<=DATE_FORMAT(?,'%Y-%m-%d') and DATE_FORMAT(should_receive_date,'%Y-%m-%d')>=DATE_FORMAT(?,'%Y-%m-%d')");
                        param.add(endTime);
                        param.add(startTime);
                    }
                }
            }


            if (ObjectUtil.isNotEmpty(cwTjParam.getTransaction())) {
                int transaction = cwTjParam.getTransaction();
                if (0!=transaction){
                    sb.append(" and transaction = ?");
                    param.add(transaction);
                }
            }
            if (ObjectUtil.isNotEmpty(cwTjParam.getAuditFlag())) {
                int auditFlag = cwTjParam.getAuditFlag();
                if (2!=auditFlag){
                    sb.append(" and audit_flag = ?");
                    param.add(auditFlag);
                }
            }
            // 根据交易方式编码 查询
            if (ObjectUtil.isNotEmpty(cwTjParam.getStateId())) {
                Long stateId = cwTjParam.getStateId();
                Long roomId = cwTjParam.getRoomId();
                if (0!=stateId&&0==roomId){
                    sb.append(" and id in (select bill_water_id from bill_water_room where room_id in (select id from bus_room where status=0 and estateid = ?))");
                    param.add(stateId);
                }
                if (0!=stateId&&0!=roomId){
                    sb.append(" and id in (select bill_water_id from bill_water_room where room_id =? )");
                    param.add(roomId);
                }
            }

            if (ObjectUtil.isNotEmpty(cwTjParam.getReceiveDateType())) {
                int receiveDateType = cwTjParam.getReceiveDateType();
                if (receiveDateType==1){
                    sb.append(" group by receive_date");
                }else {
                    sb.append(" group by should_receive_date");
                }
            }
        }
        List<CwTjRes> res=new ArrayList<>();
        String jySQL="select receive_date as date,sum(amount) as value, 'jy' as type from bill_water  where 1=1 " +sb.toString();
        List<CwTjRes> jyList = dbUtils.query(jySQL, CwTjRes.class,param.toArray());
        res.addAll(jyList);
        String srSQL="select receive_date as date,sum(amount) as value, 'sr' as type  from bill_water where bill_in_out_type = 1 "+sb.toString();
        List<CwTjRes> srList = dbUtils.query(srSQL, CwTjRes.class,param.toArray());
        CwTjRes re=null;
        List<CwTjRes> collect=null;
        List<CwTjRes> srListExt =new ArrayList<>();
        for (int i = 0; i < jyList.size(); i++) {
            CwTjRes  cwTjRes = jyList.get(i);
            collect = srList.stream().filter(item -> item.getDate().equals(cwTjRes.getDate())).collect(Collectors.toList());
            if (null==collect||collect.size()==0){
                re=new CwTjRes();
                re.setType("sr");
                re.setDate(cwTjRes.getDate());
                re.setValue("0");
                srListExt.add(re);
            }
        }
        res.addAll(srList);
        res.addAll(srListExt);
        String zcSQL="select receive_date as date,sum(amount) as value, 'zc' as type  from bill_water where bill_in_out_type = 2 "+sb.toString();
        List<CwTjRes> zcList = dbUtils.query(zcSQL, CwTjRes.class,param.toArray());
        List<CwTjRes> zcListExt =new ArrayList<>();
        for (int i = 0; i < jyList.size(); i++) {
            CwTjRes cwTjRes = jyList.get(i);
            collect = zcList.stream().filter(item -> item.getDate().equals(cwTjRes.getDate())).collect(Collectors.toList());
            if (null==collect||collect.size()==0){
                re=new CwTjRes();
                re.setType("zc");
                re.setDate(cwTjRes.getDate());
                re.setValue("0");
                zcListExt.add(re);
            }
        }
        res.addAll(zcList);
        res.addAll(zcListExt);
        return res;
    }

    @Override
    public CwTjAllRes cwTjHz(CwTjParam cwTjParam) {
        CwTjAllRes re=new CwTjAllRes();
        StringBuilder sb=new StringBuilder();
        ArrayList<Object> param = CollectionUtil.newArrayList();
        if (ObjectUtil.isNotNull(cwTjParam)) {
            String startTime = cwTjParam.getDateStrStart();
            String endTime = cwTjParam.getDateStrEnd();
            if(ObjectUtil.isNotEmpty(startTime)&&ObjectUtil.isNotEmpty(endTime)) {
                // 根据房间名 查询
                if (ObjectUtil.isNotEmpty(cwTjParam.getReceiveDateType())) {
                    int receiveDateType = cwTjParam.getReceiveDateType();
                    if (receiveDateType==1){
                        sb.append(" and  DATE_FORMAT(receive_date,'%Y-%m-%d')<=DATE_FORMAT(?,'%Y-%m-%d') and DATE_FORMAT(receive_date,'%Y-%m-%d')>=DATE_FORMAT(?,'%Y-%m-%d')");
                        param.add(endTime);
                        param.add(startTime);
                    }else {
                        sb.append(" and  DATE_FORMAT(should_receive_date,'%Y-%m-%d')<=DATE_FORMAT(?,'%Y-%m-%d') and DATE_FORMAT(should_receive_date,'%Y-%m-%d')>=DATE_FORMAT(?,'%Y-%m-%d')");
                        param.add(endTime);
                        param.add(startTime);
                    }
                }
            }


            if (ObjectUtil.isNotEmpty(cwTjParam.getTransaction())) {
                int transaction = cwTjParam.getTransaction();
                if (0!=transaction){
                    sb.append(" and transaction = ?");
                    param.add(transaction);
                }
            }
            if (ObjectUtil.isNotEmpty(cwTjParam.getAuditFlag())) {
                int auditFlag = cwTjParam.getAuditFlag();
                if (2!=auditFlag){
                    sb.append(" and audit_flag = ?");
                    param.add(auditFlag);
                }
            }
            // 根据交易方式编码 查询
            if (ObjectUtil.isNotEmpty(cwTjParam.getStateId())) {
                Long stateId = cwTjParam.getStateId();
                Long roomId = cwTjParam.getRoomId();
                if (0!=stateId&&0==roomId){
                    sb.append(" and id in (select bill_water_id from bill_water_room where room_id in (select id from bus_room where status=0 and estateid = ?))");
                    param.add(stateId);
                }
                if (0!=stateId&&0!=roomId){
                    sb.append(" and id in (select bill_water_id from bill_water_room where room_id =? )");
                    param.add(roomId);
                }
            }
        }
        String jySQL="select ifnull(sum(amount),0) as value from bill_water  where 1=1 " +sb.toString();
        String srSQL="select ifnull(sum(amount),0) as value  from bill_water where bill_in_out_type = 1 "+sb.toString();
        String zcSQL="select ifnull(sum(amount),0) as value  from bill_water where bill_in_out_type = 2 "+sb.toString();
        Long jyvalue = dbUtils.getLongValue(jySQL,param.toArray());
        Long srvalue = dbUtils.getLongValue(srSQL,param.toArray());
        Long zcCalue = dbUtils.getLongValue(zcSQL,param.toArray());
        re.setJy(jyvalue);
        re.setSr(srvalue);
        re.setZc(zcCalue);
        return re;
    }
}
